Skip Headers

Oracle® Database Administrator's Guide 10g Release 2 (10.2) Part Number B14231-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 20 Managing Views, Sequences, and Synonyms

This chapter describes the management of views, sequences, and synonyms and contains the following topics:

- Managing Views

- Managing Sequences

- Managing Synonyms

- Viewing Information About Views, Synonyms, and Sequences

## Managing Views

This section describes aspects of managing views, and contains the following topics:

- About Views

- Creating Views

- Replacing Views

- Using Views in Queries

- Updating a Join View

- Altering Views

- Dropping Views

### About Views

A view is a logical representation of another table or combination of tables. A view derives its data from the tables on which it is based. These tables are called base tables. Base tables might in turn be actual tables or might be views themselves. All operations performed on a view actually affect the base table of the view. You can use views in almost the same way as tables. You can query, update, insert into, and delete from views, just as you can standard tables.

Views can provide a different representation (such as subsets or supersets) of the data that resides within other tables and views. Views are very powerful because they allow you to tailor the presentation of data to different types of users.

See Also: Oracle Database Concepts for a more complete description of views

### Creating Views

To create a view, you must meet the following requirements:

- To create a view in your schema, you must have the `CREATE VIEW` privilege. To create a view in another user's schema, you must have the `CREATE ANY VIEW` system privilege. You can acquire these privileges explicitly or through a role.

- The owner of the view (whether it is you or another user) must have been explicitly granted privileges to access all objects referenced in the view definition. The owner cannot have obtained these privileges through roles. Also, the functionality of the view depends on the privileges of the view owner. For example, if the owner of the view has only the `INSERT` privilege for Scott's `emp` table, then the view can be used only to insert new rows into the `emp` table, not to `SELECT`, `UPDATE`, or `DELETE` rows.

- If the owner of the view intends to grant access to the view to other users, the owner must have received the object privileges to the base objects with the `GRANT OPTION` or the system privileges with the `ADMIN OPTION`.

You can create views using the `CREATE VIEW` statement. Each view is defined by a query that references tables, materialized views, or other views. As with all subqueries, the query that defines a view cannot contain the `FOR UPDATE` clause.

The following statement creates a view on a subset of data in the `emp` table:

    CREATE VIEW sales_staff AS
          SELECT empno, ename, deptno
          FROM emp
          WHERE deptno = 10
        WITH CHECK OPTION CONSTRAINT sales_staff_cnst;

The query that defines the `sales_staff` view references only rows in department 10. Furthermore, the `CHECK OPTION` creates the view with the constraint (named `sales_staff_cnst`) that `INSERT` and `UPDATE` statements issued against the view cannot result in rows that the view cannot select. For example, the following `INSERT` statement successfully inserts a row into the `emp` table by means of the `sales_staff` view, which contains all rows with department number 10:

    INSERT INTO sales_staff VALUES (7584, 'OSTER', 10);

However, the following `INSERT` statement returns an error because it attempts to insert a row for department number 30, which cannot be selected using the `sales_staff` view:

    INSERT INTO sales_staff VALUES (7591, 'WILLIAMS', 30);

The view could have been constructed specifying the `WITH READ ONLY` clause, which prevents any updates, inserts, or deletes from being done to the base table through the view. If no `WITH` clause is specified, the view, with some restrictions, is inherently updatable.

See Also: Oracle Database SQL Reference for syntax and semantics of the `CREATE VIEW` statement

#### Join Views

You can also create views that specify more than one base table or view in the `FROM` clause. These are called join views. The following statement creates the `division1_staff` view that joins data from the `emp` and `dept` tables:

    CREATE VIEW division1_staff AS
          SELECT ename, empno, job, dname
          FROM emp, dept
          WHERE emp.deptno IN (10, 30)
             AND emp.deptno = dept.deptno;

An updatable join view is a join view where `UPDATE`, `INSERT`, and `DELETE` operations are allowed. See "Updating a Join View" for further discussion.

#### Expansion of Defining Queries at View Creation Time

When a view is created, Oracle Database expands any wildcard (*) in a top-level view query into a column list. The resulting query is stored in the data dictionary; any subqueries are left intact. The column names in an expanded column list are enclosed in quote marks to account for the possibility that the columns of the base object were originally entered with quotes and require them for the query to be syntactically correct.

As an example, assume that the `dept` view is created as follows:

    CREATE VIEW dept AS SELECT * FROM scott.dept;

The database stores the defining query of the `dept` view as:

    SELECT "DEPTNO", "DNAME", "LOC" FROM scott.dept;

Views created with errors do not have wildcards expanded. However, if the view is eventually compiled without errors, wildcards in the defining query are expanded.

#### Creating Views with Errors

If there are no syntax errors in a `CREATE VIEW` statement, the database can create the view even if the defining query of the view cannot be executed. In this case, the view is considered "created with errors." For example, when a view is created that refers to a nonexistent table or an invalid column of an existing table, or when the view owner does not have the required privileges, the view can be created anyway and entered into the data dictionary. However, the view is not yet usable.

To create a view with errors, you must include the `FORCE` clause of the `CREATE VIEW` statement.

    CREATE FORCE VIEW AS ...;

By default, views with errors are created as `INVALID`. When you try to create such a view, the database returns a message indicating the view was created with errors. If conditions later change so that the query of an invalid view can be executed, the view can be recompiled and be made valid (usable). For information changing conditions and their impact on views, see "Managing Object Dependencies".

### Replacing Views

To replace a view, you must have all of the privileges required to drop and create a view. If the definition of a view must change, the view must be replaced; you cannot use an `ALTER VIEW` statement to change the definition of a view. You can replace views in the following ways:

- You can drop and re-create the view.

  Caution: When a view is dropped, all grants of corresponding object privileges are revoked from roles and users. After the view is re-created, privileges must be regranted.

- You can redefine the view with a `CREATE VIEW` statement that contains the `OR REPLACE` clause. The `OR REPLACE` clause replaces the current definition of a view and preserves the current security authorizations. For example, assume that you created the `sales_staff` view as shown earlier, and, in addition, you granted several object privileges to roles and other users. However, now you need to redefine the `sales_staff` view to change the department number specified in the `WHERE` clause. You can replace the current version of the `sales_staff` view with the following statement:

      CREATE OR REPLACE VIEW sales_staff AS
           SELECT empno, ename, deptno
           FROM emp
           WHERE deptno = 30
           WITH CHECK OPTION CONSTRAINT sales_staff_cnst;

Before replacing a view, consider the following effects:

- Replacing a view replaces the view definition in the data dictionary. All underlying objects referenced by the view are not affected.

- If a constraint in the `CHECK OPTION` was previously defined but not included in the new view definition, the constraint is dropped.

- All views and PL/SQL program units dependent on a replaced view become invalid (not usable). See "Managing Object Dependencies" for more information on how the database manages such dependencies.

### Using Views in Queries

To issue a query or an `INSERT,` `UPDATE`, or `DELETE` statement against a view, you must have the `SELECT,` `INSERT,` `UPDATE,` or `DELETE` object privilege for the view, respectively, either explicitly or through a role.

Views can be queried in the same manner as tables. For example, to query the `Division1_staff` view, enter a valid `SELECT` statement that references the view:

    SELECT * FROM Division1_staff;

    ENAME        EMPNO       JOB             DNAME
    ------------------------------------------------------
    CLARK         7782       MANAGER         ACCOUNTING
    KING          7839       PRESIDENT       ACCOUNTING
    MILLER        7934       CLERK           ACCOUNTING
    ALLEN         7499       SALESMAN        SALES
    WARD          7521       SALESMAN        SALES
    JAMES         7900       CLERK           SALES
    TURNER        7844       SALESMAN        SALES
    MARTIN        7654       SALESMAN        SALES
    BLAKE         7698       MANAGER         SALES

With some restrictions, rows can be inserted into, updated in, or deleted from a base table using a view. The following statement inserts a new row into the `emp` table using the `sales_staff` view:

    INSERT INTO sales_staff
        VALUES (7954, 'OSTER', 30);

Restrictions on DML operations for views use the following criteria in the order listed:

1. If a view is defined by a query that contains `SET` or `DISTINCT` operators, a `GROUP` `BY` clause, or a group function, then rows cannot be inserted into, updated in, or deleted from the base tables using the view.

2. If a view is defined with `WITH` `CHECK` `OPTION`, a row cannot be inserted into, or updated in, the base table (using the view), if the view cannot select the row from the base table.

3. If a `NOT` `NULL` column that does not have a `DEFAULT` clause is omitted from the view, then a row cannot be inserted into the base table using the view.

4. If the view was created by using an expression, such as `DECODE(deptno, 10, "SALES", ...)`, then rows cannot be inserted into or updated in the base table using the view.

The constraint created by `WITH` `CHECK` `OPTION` of the `sales_staff` view only allows rows that have a department number of 30 to be inserted into, or updated in, the `emp` table. Alternatively, assume that the `sales_staff` view is defined by the following statement (that is, excluding the `deptno` column):

    CREATE VIEW sales_staff AS
        SELECT empno, ename
        FROM emp
        WHERE deptno = 10
        WITH CHECK OPTION CONSTRAINT sales_staff_cnst;

Considering this view definition, you can update the `empno` or `ename` fields of existing records, but you cannot insert rows into the `emp` table through the `sales_staff` view because the view does not let you alter the `deptno` field. If you had defined a `DEFAULT` value of 10 on the `deptno` field, then you could perform inserts.

When a user attempts to reference an invalid view, the database returns an error message to the user:

    ORA-04063: view 'view_name' has errors

This error message is returned when a view exists but is unusable due to errors in its query (whether it had errors when originally created or it was created successfully but became unusable later because underlying objects were altered or dropped).

### Updating a Join View

An updatable join view (also referred to as a modifiable join view) is a view that contains more than one table in the top-level `FROM` clause of the `SELECT` statement, and is not restricted by the `WITH READ ONLY` clause.

The rules for updatable join views are shown in the following table. Views that meet these criteria are said to be inherently updatable.

Rule | Description
--- | ---
General Rule | Any `INSERT`, `UPDATE`, or `DELETE` operation on a join view can modify only one underlying base table at a time.
`UPDATE` Rule | All updatable columns of a join view must map to columns of a key-preserved table. See "Key-Preserved Tables" for a discussion of key-preserved tables. If the view is defined with the `WITH CHECK OPTION` clause, then all join columns and all columns of repeated tables are not updatable.
`DELETE` Rule | Rows from a join view can be deleted as long as there is exactly one key-preserved table in the join. The key preserved table can be repeated in the `FROM` clause. If the view is defined with the `WITH CHECK OPTION` clause and the key preserved table is repeated, then the rows cannot be deleted from the view.
`INSERT` Rule | An `INSERT` statement must not explicitly or implicitly refer to the columns of a non-key-preserved table. If the join view is defined with the `WITH CHECK OPTION` clause, `INSERT` statements are not permitted.

There are data dictionary views that indicate whether the columns in a join view are inherently updatable. See "Using the UPDATABLE_ COLUMNS Views" for descriptions of these views.

Note: There are some additional restrictions and conditions that can affect whether a join view is inherently updatable. Specifics are listed in the description of the `CREATE VIEW` statement in the Oracle Database SQL Reference. If a view is not inherently updatable, it can be made updatable by creating an `INSTEAD` `OF` trigger on it. This is described in Oracle Database Application Developer's Guide - Fundamentals. Additionally, if a view is a join on other nested views, then the other nested views must be mergeable into the top level view. For a discussion of mergeable and unmergeable views, and more generally, how the optimizer optimizes statements that reference views, see the Oracle Database Performance Tuning Guide.

Examples illustrating the rules for inherently updatable join views, and a discussion of key-preserved tables, are presented in following sections. The examples in these sections work only if you explicitly define the primary and foreign keys in the tables, or define unique indexes. The following statements create the appropriately constrained table definitions for `emp` and `dept`.

    CREATE TABLE dept (
          deptno        NUMBER(4) PRIMARY KEY,
          dname         VARCHAR2(14),
          loc           VARCHAR2(13));

    CREATE TABLE emp (
          empno        NUMBER(4) PRIMARY KEY,
          ename        VARCHAR2(10),
          job          VARCHAR2(9),
          mgr          NUMBER(4),
          sal          NUMBER(7,2),
          comm         NUMBER(7,2),
          deptno       NUMBER(2),
          FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO));

You could also omit the primary and foreign key constraints listed in the preceding example, and create a `UNIQUE INDEX` on `dept (deptno)` to make the following examples work.

The following statement created the `emp_dept` join view which is referenced in the examples:

    CREATE VIEW emp_dept AS
          SELECT emp.empno, emp.ename, emp.deptno, emp.sal, dept.dname, dept.loc
          FROM emp, dept
          WHERE emp.deptno = dept.deptno
             AND dept.loc IN ('DALLAS', 'NEW YORK', 'BOSTON');

#### Key-Preserved Tables

The concept of a key-preserved table is fundamental to understanding the restrictions on modifying join views. A table is key-preserved if every key of the table can also be a key of the result of the join. So, a key-preserved table has its keys preserved through a join.

Note: It is not necessary that the key or keys of a table be selected for it to be key preserved. It is sufficient that if the key or keys were selected, then they would also be keys of the result of the join.

The key-preserving property of a table does not depend on the actual data in the table. It is, rather, a property of its schema. For example, if in the `emp` table there was at most one employee in each department, then `deptno` would be unique in the result of a join of `emp` and `dept`, but `dept` would still not be a key-preserved table.

If you select all rows from `emp_dept`, the results are:

    EMPNO      ENAME      DEPTNO  DNAME          LOC
    ---------- ---------- ------- -------------- -----------
          7782 CLARK           10 ACCOUNTING     NEW YORK
          7839 KING            10 ACCOUNTING     NEW YORK
          7934 MILLER          10 ACCOUNTING     NEW YORK
          7369 SMITH           20 RESEARCH       DALLAS
          7876 ADAMS           20 RESEARCH       DALLAS
          7902 FORD            20 RESEARCH       DALLAS
          7788 SCOTT           20 RESEARCH       DALLAS
          7566 JONES           20 RESEARCH       DALLAS
    8 rows selected.

In this view, `emp` is a key-preserved table, because `empno` is a key of the `emp` table, and also a key of the result of the join. `dept` is not a key-preserved table, because although `deptno` is a key of the `dept` table, it is not a key of the join.

#### DML Statements and Join Views

The general rule is that any `UPDATE`, `DELETE`, or `INSERT` statement on a join view can modify only one underlying base table. The following examples illustrate rules specific to `UPDATE`, `DELETE`, and `INSERT` statements.

##### UPDATE Statements

The following example shows an `UPDATE` statement that successfully modifies the `emp_dept` view:

    UPDATE emp_dept
         SET sal = sal * 1.10
         WHERE deptno = 10;

The following `UPDATE` statement would be disallowed on the `emp_dept` view:

    UPDATE emp_dept
         SET loc = 'BOSTON'
         WHERE ename = 'SMITH';

This statement fails with an error (`ORA-01779` `cannot modify a column which maps to a non key-preserved table`), because it attempts to modify the base `dept` table, and the `dept` table is not key-preserved in the `emp_dept` view.

In general, all updatable columns of a join view must map to columns of a key-preserved table. If the view is defined using the `WITH CHECK OPTION` clause, then all join columns and all columns taken from tables that are referenced more than once in the view are not modifiable.

So, for example, if the `emp_dept` view were defined using `WITH CHECK OPTION`, the following `UPDATE` statement would fail:

    UPDATE emp_dept
         SET deptno = 10
         WHERE ename = 'SMITH';

The statement fails because it is trying to update a join column.

See Also: Oracle Database SQL Reference for syntax and additional information about the `UPDATE` statement

##### DELETE Statements

You can delete from a join view provided there is one and only one key-preserved table in the join. The key-preserved table can be repeated in the `FROM` clause.

The following `DELETE` statement works on the `emp_dept` view:

    DELETE FROM emp_dept
         WHERE ename = 'SMITH';

This `DELETE` statement on the `emp_dept` view is legal because it can be translated to a `DELETE` operation on the base `emp` table, and because the `emp` table is the only key-preserved table in the join.

In the following view, a `DELETE` operation is permitted, because although there are two key-preserved tables, they are the same table. That is, the key-preserved table is repeated. In this case, the delete statement operates on the first table in the `FROM` list (`e1`, in this example):

    CREATE VIEW emp_emp AS
         SELECT e1.ename, e2.empno, e2.deptno
         FROM emp e1, emp e2
         WHERE e1.empno = e2.empno;

If a view is defined using the `WITH CHECK OPTION` clause and the key-preserved table is repeated, rows cannot be deleted from such a view.

    CREATE VIEW emp_mgr AS
         SELECT e1.ename, e2.ename mname
         FROM emp e1, emp e2
         WHERE e1.mgr = e2.empno
         WITH CHECK OPTION;

See Also: Oracle Database SQL Reference for syntax and additional information about the `DELETE` statement

##### INSERT Statements

The following `INSERT` statement on the `emp_dept` view succeeds:

    INSERT INTO emp_dept (ename, empno, deptno)
       VALUES ('KURODA', 9010, 40);

This statement works because only one key-preserved base table is being modified (`emp`), and 40 is a valid `deptno` in the `dept` table (thus satisfying the `FOREIGN KEY` integrity constraint on the `emp` table).

An `INSERT` statement, such as the following, would fail for the same reason that such an `UPDATE` on the base `emp` table would fail: the `FOREIGN KEY` integrity constraint on the `emp` table is violated (because there is no `deptno` 77).

    INSERT INTO emp_dept (ename, empno, deptno)
       VALUES ('KURODA', 9010, 77);

The following `INSERT` statement would fail with an error (`ORA-01776` `cannot modify more than one base table through a join view`):

    INSERT INTO emp_dept (empno, ename, loc)
       VALUES (9010, 'KURODA', 'BOSTON');

An `INSERT` cannot implicitly or explicitly refer to columns of a non-key-preserved table. If the join view is defined using the `WITH CHECK OPTION` clause, then you cannot perform an `INSERT` to it.

See Also: Oracle Database SQL Reference for syntax and additional information about the `INSERT` statement

#### Updating Views That Involve Outer Joins

Views that involve outer joins are modifiable in some cases. For example:

    CREATE VIEW emp_dept_oj1 AS
        SELECT empno, ename, e.deptno, dname, loc
        FROM emp e, dept d
        WHERE e.deptno = d.deptno (+);

The statement:

    SELECT * FROM emp_dept_oj1;

Results in:

    EMPNO   ENAME      DEPTNO  DNAME          LOC
    ------- ---------- ------- -------------- -------------
    7369    SMITH      40      OPERATIONS     BOSTON
    7499    ALLEN      30      SALES          CHICAGO
    7566    JONES      20      RESEARCH       DALLAS
    7654    MARTIN     30      SALES          CHICAGO
    7698    BLAKE      30      SALES          CHICAGO
    7782    CLARK      10      ACCOUNTING     NEW YORK
    7788    SCOTT      20      RESEARCH       DALLAS
    7839    KING       10      ACCOUNTING     NEW YORK
    7844    TURNER     30      SALES          CHICAGO
    7876    ADAMS      20      RESEARCH       DALLAS
    7900    JAMES      30      SALES          CHICAGO
    7902    FORD       20      RESEARCH       DALLAS
    7934    MILLER     10      ACCOUNTING     NEW YORK
    7521    WARD       30      SALES          CHICAGO
    14 rows selected.

Columns in the base `emp` table of `emp_dept_oj1` are modifiable through the view, because `emp` is a key-preserved table in the join.

The following view also contains an outer join:

    CREATE VIEW emp_dept_oj2 AS
    SELECT e.empno, e.ename, e.deptno, d.dname, d.loc
    FROM emp e, dept d
    WHERE e.deptno (+) = d.deptno;

The following statement:

    SELECT * FROM emp_dept_oj2;

Results in:

    EMPNO      ENAME      DEPTNO    DNAME          LOC
    ---------- ---------- --------- -------------- ----
    7782       CLARK      10        ACCOUNTING     NEW YORK
    7839       KING       10        ACCOUNTING     NEW YORK
    7934       MILLER     10        ACCOUNTING     NEW YORK
    7369       SMITH      20        RESEARCH       DALLAS
    7876       ADAMS      20        RESEARCH       DALLAS
    7902       FORD       20        RESEARCH       DALLAS
    7788       SCOTT      20        RESEARCH       DALLAS
    7566       JONES      20        RESEARCH       DALLAS
    7499       ALLEN      30        SALES          CHICAGO
    7698       BLAKE      30        SALES          CHICAGO
    7654       MARTIN     30        SALES          CHICAGO
    7900       JAMES      30        SALES          CHICAGO
    7844       TURNER     30        SALES          CHICAGO
    7521       WARD       30        SALES          CHICAGO
                                    OPERATIONS     BOSTON
    15 rows selected.

In this view, `emp` is no longer a key-preserved table, because the `empno` column in the result of the join can have nulls (the last row in the preceding `SELECT` statement). So, `UPDATE`, `DELETE`, and `INSERT` operations cannot be performed on this view.

In the case of views containing an outer join on other nested views, a table is key preserved if the view or views containing the table are merged into their outer views, all the way to the top. A view which is being outer-joined is currently merged only if it is "simple." For example:

    SELECT col1, col2, ... FROM T;

The select list of the view has no expressions, and there is no `WHERE` clause.

Consider the following set of views:

    CREATE VIEW emp_v AS
        SELECT empno, ename, deptno
            FROM emp;
    CREATE VIEW emp_dept_oj1 AS
        SELECT e.*, Loc, d.dname
            FROM emp_v e, dept d
                WHERE e.deptno = d.deptno (+);

In these examples, `emp_v` is merged into `emp_dept_oj1` because `emp_v` is a simple view, and so `emp` is a key-preserved table. But if `emp_v` is changed as follows:

    CREATE VIEW emp_v_2 AS
        SELECT empno, ename, deptno
            FROM emp
                WHERE sal > 1000;

Then, because of the presence of the `WHERE` clause, `emp_v_2` cannot be merged into `emp_dept_oj1`, and hence `emp` is no longer a key-preserved table.

If you are in doubt whether a view is modifiable, then you can select from the `USER_UPDATABLE_COLUMNS` view to see if it is. For example:

    SELECT owner, table_name, column_name, updatable FROM USER_UPDATABLE_COLUMNS
         WHERE TABLE_NAME = 'EMP_DEPT_VIEW';

This returns output similar to the following:

    OWNER       TABLE_NAME      COLUMN_NAM      UPD
    ----------  ----------      ----------      ---
    SCOTT       EMP_DEPT_V      EMPNO           NO
    SCOTT       EMP_DEPT_V      ENAME           NO
    SCOTT       EMP_DEPT_V      DEPTNO          NO
    SCOTT       EMP_DEPT_V      DNAME           NO
    SCOTT       EMP_DEPT_V      LOC             NO
    5 rows selected.

#### Using the UPDATABLE_ COLUMNS Views

The views described in the following table can assist you to identify inherently updatable join views.

View | Description
--- | ---
`DBA_UPDATABLE_COLUMNS` | Shows all columns in all tables and views that are modifiable.
`ALL_UPDATABLE_COLUMNS` | Shows all columns in all tables and views accessible to the user that are modifiable.
`USER_UPDATABLE_COLUMNS` | Shows all columns in all tables and views in the user's schema that are modifiable.

The updatable columns in view `emp_dept` are shown below.

    SELECT COLUMN_NAME, UPDATABLE
          FROM USER_UPDATABLE_COLUMNS
          WHERE TABLE_NAME = 'EMP_DEPT';

    COLUMN_NAME                    UPD
    ------------------------------ ---
    EMPNO                          YES
    ENAME                          YES
    DEPTNO                         YES
    SAL                            YES
    DNAME                          NO
    LOC                            NO

    6 rows selected.

See Also: Oracle Database Reference for complete descriptions of the updatable column views

### Altering Views

You use the `ALTER VIEW` statement only to explicitly recompile a view that is invalid. If you want to change the definition of a view, see "Replacing Views".

The `ALTER VIEW` statement lets you locate recompilation errors before run time. To ensure that the alteration does not affect the view or other objects that depend on it, you can explicitly recompile a view after altering one of its base tables.

To use the `ALTER VIEW` statement, the view must be in your schema, or you must have the `ALTER ANY TABLE` system privilege.

See Also: Oracle Database SQL Reference for syntax and additional information about the `ALTER VIEW` statement

### Dropping Views

You can drop any view contained in your schema. To drop a view in another user's schema, you must have the `DROP ANY VIEW` system privilege. Drop a view using the `DROP VIEW` statement. For example, the following statement drops the `emp_dept` view:

    DROP VIEW emp_dept;

See Also: Oracle Database SQL Reference for syntax and additional information about the `DROP VIEW` statement

## Managing Sequences

This section describes aspects of managing sequences, and contains the following topics:

- About Sequences

- Creating Sequences

- Altering Sequences

- Using Sequences

- Dropping Sequences

### About Sequences

Sequences are database objects from which multiple users can generate unique integers. The sequence generator generates sequential numbers, which can help to generate unique primary keys automatically, and to coordinate keys across multiple rows or tables.

Without sequences, sequential values can only be produced programmatically. A new primary key value can be obtained by selecting the most recently produced value and incrementing it. This method requires a lock during the transaction and causes multiple users to wait for the next value of the primary key; this waiting is known as serialization. If developers have such constructs in applications, then you should encourage the developers to replace them with access to sequences. Sequences eliminate serialization and improve the concurrency of an application.

See Also: Oracle Database Concepts for a more complete description of sequences

### Creating Sequences

To create a sequence in your schema, you must have the `CREATE SEQUENCE` system privilege. To create a sequence in another user's schema, you must have the `CREATE ANY SEQUENCE` privilege.

Create a sequence using the `CREATE SEQUENCE` statement. For example, the following statement creates a sequence used to generate employee numbers for the `empno` column of the `emp` table:

    CREATE SEQUENCE emp_sequence
          INCREMENT BY 1
          START WITH 1
          NOMAXVALUE
          NOCYCLE
          CACHE 10;

Notice that several parameters can be specified to control the function of sequences. You can use these parameters to indicate whether the sequence is ascending or descending, the starting point of the sequence, the minimum and maximum values, and the interval between sequence values. The `NOCYCLE` option indicates that the sequence cannot generate more values after reaching its maximum or minimum value.

The `CACHE` clause preallocates a set of sequence numbers and keeps them in memory so that sequence numbers can be accessed faster. When the last of the sequence numbers in the cache has been used, the database reads another set of numbers into the cache.

The database might skip sequence numbers if you choose to cache a set of sequence numbers. For example, when an instance abnormally shuts down (for example, when an instance failure occurs or a `SHUTDOWN ABORT` statement is issued), sequence numbers that have been cached but not used are lost. Also, sequence numbers that have been used but not saved are lost as well. The database might also skip cached sequence numbers after an export and import. See Oracle Database Utilities for details.

See Also: Oracle Database SQL Reference for the `CREATE SEQUENCE` statement syntax Oracle Real Application Clusters Deployment and Performance Guide for information about how caching sequence numbers improves performance in an Oracle Real Application Clusters environment

### Altering Sequences

To alter a sequence, your schema must contain the sequence, or you must have the `ALTER ANY SEQUENCE` system privilege. You can alter a sequence to change any of the parameters that define how it generates sequence numbers except the sequence starting number. To change the starting point of a sequence, drop the sequence and then re-create it.

Alter a sequence using the `ALTER SEQUENCE` statement. For example, the following statement alters the `emp_sequence`:

    ALTER SEQUENCE emp_sequence
        INCREMENT BY 10
        MAXVALUE 10000
        CYCLE
        CACHE 20;

See Also: Oracle Database SQL Reference for syntax and additional information about the `ALTER SEQUENCE` statement

### Using Sequences

To use a sequence, your schema must contain the sequence or you must have been granted the `SELECT` object privilege for another user's sequence. Once a sequence is defined, it can be accessed and incremented by multiple users (who have `SELECT` object privilege for the sequence containing the sequence) with no waiting. The database does not wait for a transaction that has incremented a sequence to complete before that sequence can be incremented again.

The examples outlined in the following sections show how sequences can be used in master/detail table relationships. Assume an order entry system is partially comprised of two tables, `orders_tab` (master table) and `line_items_tab` (detail table), that hold information about customer orders. A sequence named `order_seq` is defined by the following statement:

    CREATE SEQUENCE Order_seq
        START WITH 1
        INCREMENT BY 1
        NOMAXVALUE
        NOCYCLE
        CACHE 20;

#### Referencing a Sequence

A sequence is referenced in SQL statements with the `NEXTVAL` and `CURRVAL` pseudocolumns; each new sequence number is generated by a reference to the sequence pseudocolumn `NEXTVAL`, while the current sequence number can be repeatedly referenced using the pseudo-column `CURRVAL`.

`NEXTVAL` and `CURRVAL` are not reserved words or keywords and can be used as pseudocolumn names in SQL statements such as `SELECT`, `INSERT`, or `UPDATE`.

##### Generating Sequence Numbers with NEXTVAL

To generate and use a sequence number, reference seq_name.`NEXTVAL`. For example, assume a customer places an order. The sequence number can be referenced in a values list. For example:

    INSERT INTO Orders_tab (Orderno, Custno)
        VALUES (Order_seq.NEXTVAL, 1032);

Or, the sequence number can be referenced in the `SET` clause of an `UPDATE` statement. For example:

    UPDATE Orders_tab
        SET Orderno = Order_seq.NEXTVAL
        WHERE Orderno = 10112;

The sequence number can also be referenced outermost `SELECT` of a query or subquery. For example:

    SELECT Order_seq.NEXTVAL FROM dual;

As defined, the first reference to `order_seq.NEXTVAL` returns the value 1. Each subsequent statement that references `order_seq.NEXTVAL` generates the next sequence number (2, 3, 4,. . .). The pseudo-column `NEXTVAL` can be used to generate as many new sequence numbers as necessary. However, only a single sequence number can be generated for each row. In other words, if `NEXTVAL` is referenced more than once in a single statement, then the first reference generates the next number, and all subsequent references in the statement return the same number.

Once a sequence number is generated, the sequence number is available only to the session that generated the number. Independent of transactions committing or rolling back, other users referencing `order_seq.NEXTVAL` obtain unique values. If two users are accessing the same sequence concurrently, then the sequence numbers each user receives might have gaps because sequence numbers are also being generated by the other user.

##### Using Sequence Numbers with CURRVAL

To use or refer to the current sequence value of your session, reference seq_name.`CURRVAL`. `CURRVAL` can only be used if seq_name.`NEXTVAL` has been referenced in the current user session (in the current or a previous transaction). `CURRVAL` can be referenced as many times as necessary, including multiple times within the same statement. The next sequence number is not generated until `NEXTVAL` is referenced. Continuing with the previous example, you would finish placing the customer's order by inserting the line items for the order:

    INSERT INTO Line_items_tab (Orderno, Partno, Quantity)
        VALUES (Order_seq.CURRVAL, 20321, 3);

    INSERT INTO Line_items_tab (Orderno, Partno, Quantity)
        VALUES (Order_seq.CURRVAL, 29374, 1);

Assuming the `INSERT` statement given in the previous section generated a new sequence number of 347, both rows inserted by the statements in this section insert rows with order numbers of 347.

##### Uses and Restrictions of NEXTVAL and CURRVAL

`CURRVAL` and `NEXTVAL` can be used in the following places:

- `VALUES` clause of `INSERT` statements

- The `SELECT` list of a `SELECT` statement

- The `SET` clause of an `UPDATE` statement

`CURRVAL` and `NEXTVAL` cannot be used in these places:

- A subquery

- A view query or materialized view query

- A `SELECT` statement with the `DISTINCT` operator

- A `SELECT` statement with a `GROUP` `BY` or `ORDER` `BY` clause

- A `SELECT` statement that is combined with another `SELECT` statement with the `UNION,` `INTERSECT`, or `MINUS` set operator

- The `WHERE` clause of a `SELECT` statement

- `DEFAULT` value of a column in a `CREATE` `TABLE` or `ALTER` `TABLE` statement

- The condition of a `CHECK` constraint

#### Caching Sequence Numbers

Sequence numbers can be kept in the sequence cache in the System Global Area (SGA). Sequence numbers can be accessed more quickly in the sequence cache than they can be read from disk.

The sequence cache consists of entries. Each entry can hold many sequence numbers for a single sequence.

Follow these guidelines for fast access to all sequence numbers:

- Be sure the sequence cache can hold all the sequences used concurrently by your applications.

- Increase the number of values for each sequence held in the sequence cache.

##### The Number of Entries in the Sequence Cache

When an application accesses a sequence in the sequence cache, the sequence numbers are read quickly. However, if an application accesses a sequence that is not in the cache, then the sequence must be read from disk to the cache before the sequence numbers are used.

If your applications use many sequences concurrently, then your sequence cache might not be large enough to hold all the sequences. In this case, access to sequence numbers might often require disk reads. For fast access to all sequences, be sure your cache has enough entries to hold all the sequences used concurrently by your applications.

##### The Number of Values in Each Sequence Cache Entry

When a sequence is read into the sequence cache, sequence values are generated and stored in a cache entry. These values can then be accessed quickly. The number of sequence values stored in the cache is determined by the `CACHE` parameter in the `CREATE` `SEQUENCE` statement. The default value for this parameter is 20.

This `CREATE` `SEQUENCE` statement creates the `seq2` sequence so that 50 values of the sequence are stored in the `SEQUENCE` cache:

    CREATE SEQUENCE seq2
        CACHE 50;

The first 50 values of `seq2` can then be read from the cache. When the 51st value is accessed, the next 50 values will be read from disk.

Choosing a high value for `CACHE` lets you access more successive sequence numbers with fewer reads from disk to the sequence cache. However, if there is an instance failure, then all sequence values in the cache are lost. Cached sequence numbers also could be skipped after an export and import if transactions continue to access the sequence numbers while the export is running.

If you use the `NOCACHE` option in the `CREATE` `SEQUENCE` statement, then the values of the sequence are not stored in the sequence cache. In this case, every access to the sequence requires a disk read. Such disk reads slow access to the sequence. This `CREATE` `SEQUENCE` statement creates the `SEQ3` sequence so that its values are never stored in the cache:

    CREATE SEQUENCE seq3
        NOCACHE;

### Dropping Sequences

You can drop any sequence in your schema. To drop a sequence in another schema, you must have the `DROP ANY SEQUENCE` system privilege. If a sequence is no longer required, you can drop the sequence using the `DROP SEQUENCE` statement. For example, the following statement drops the `order_seq` sequence:

    DROP SEQUENCE order_seq;

When a sequence is dropped, its definition is removed from the data dictionary. Any synonyms for the sequence remain, but return an error when referenced.

See Also: Oracle Database SQL Reference for syntax and additional information about the `DROP SEQUENCE` statement

## Managing Synonyms

This section describes aspects of managing synonyms, and contains the following topics:

- About Synonyms

- Creating Synonyms

- Using Synonyms in DML Statements

- Dropping Synonyms

### About Synonyms

A synonym is an alias for a schema object. Synonyms can provide a level of security by masking the name and owner of an object and by providing location transparency for remote objects of a distributed database. Also, they are convenient to use and reduce the complexity of SQL statements for database users.

Synonyms allow underlying objects to be renamed or moved, where only the synonym needs to be redefined and applications based on the synonym continue to function without modification.

You can create both public and private synonyms. A public synonym is owned by the special user group named `PUBLIC` and is accessible to every user in a database. A private synonym is contained in the schema of a specific user and available only to the user and the user's grantees.

See Also: Oracle Database Concepts for a more complete description of synonyms

### Creating Synonyms

To create a private synonym in your own schema, you must have the `CREATE SYNONYM` privilege. To create a private synonym in another user's schema, you must have the `CREATE ANY SYNONYM` privilege. To create a public synonym, you must have the `CREATE PUBLIC SYNONYM` system privilege.

Create a synonym using the `CREATE SYNONYM` statement. The underlying schema object need not exist, nor do you need privileges to access the object. The following statement creates a public synonym named `public_emp` on the `emp` table contained in the schema of `jward`:

    CREATE PUBLIC SYNONYM public_emp FOR jward.emp

When you create a synonym for a remote procedure or function, you must qualify the remote object with its schema name. Alternatively, you can create a local public synonym on the database where the remote object resides, in which case the database link must be included in all subsequent calls to the procedure or function.

See Also: Oracle Database SQL Reference for syntax and additional information about the `CREATE SYNONYM` statement

### Using Synonyms in DML Statements

You can successfully use any private synonym contained in your schema or any public synonym, assuming that you have the necessary privileges to access the underlying object, either explicitly, from an enabled role, or from `PUBLIC`. You can also reference any private synonym contained in another schema if you have been granted the necessary object privileges for the private synonym.

You can only reference another user's synonym using the object privileges that you have been granted. For example, if you have the `SELECT` privilege for the `jward`.`emp` synonym, then you can query the `jward`.`emp` synonym, but you cannot insert rows using the `jward`.`emp` synonym.

A synonym can be referenced in a DML statement the same way that the underlying object of the synonym can be referenced. For example, if a synonym named `emp` refers to a table or view, then the following statement is valid:

    INSERT INTO emp (empno, ename, job)
        VALUES (emp_sequence.NEXTVAL, 'SMITH', 'CLERK');

If the synonym named `fire_emp` refers to a standalone procedure or package procedure, then you could execute it with the command

    EXECUTE Fire_emp(7344);

### Dropping Synonyms

You can drop any private synonym in your own schema. To drop a private synonym in another user's schema, you must have the `DROP ANY SYNONYM` system privilege. To drop a public synonym, you must have the `DROP PUBLIC SYNONYM` system privilege.

Drop a synonym that is no longer required using `DROP SYNONYM` statement. To drop a private synonym, omit the `PUBLIC` keyword. To drop a public synonym, include the `PUBLIC` keyword.

For example, the following statement drops the private synonym named `emp`:

    DROP SYNONYM emp;

The following statement drops the public synonym named `public_emp`:

    DROP PUBLIC SYNONYM public_emp;

When you drop a synonym, its definition is removed from the data dictionary. All objects that reference a dropped synonym remain. However, they become invalid (not usable). For more information about how dropping synonyms can affect other schema objects, see "Managing Object Dependencies".

See Also: Oracle Database SQL Reference for syntax and additional information about the `DROP SYNONYM` statement

## Viewing Information About Views, Synonyms, and Sequences

The following views display information about views, synonyms, and sequences:

View | Description
--- | ---
`DBA_VIEWS` `ALL_VIEWS` `USER_VIEWS` | `DBA` view describes all views in the database. `ALL` view is restricted to views accessible to the current user. `USER` view is restricted to views owned by the current user.
`DBA_SYNONYMS` `ALL_SYNONYMS` `USER_SYNONYMS` | These views describe synonyms.
`DBA_SEQUENCES` `ALL_SEQUENCES` `USER_SEQUENCES` | These views describe sequences.
`DBA_UPDATABLE_COLUMNS` `ALL_UPDATABLE_COLUMNS` `USER_UPDATABLE_COLUMNS` | These views describe all columns in join views that are updatable.

See Also: Oracle Database Reference for complete descriptions of these views